package com.beaut.project.util;
import java.util.Iterator;
import java.util.LinkedHashMap;
import java.util.List;
import java.util.Map;
import java.util.Map.Entry;
import org.apache.poi.ss.usermodel.Cell;
import org.apache.poi.ss.usermodel.CellStyle;
import org.apache.poi.ss.usermodel.CellValue;
import org.apache.poi.ss.usermodel.DateUtil;
import org.apache.poi.ss.usermodel.Font;
import org.apache.poi.ss.usermodel.IndexedColors;
import org.apache.poi.ss.usermodel.Row;
import org.apache.poi.ss.usermodel.Sheet;
import org.apache.poi.ss.usermodel.Workbook;
import org.apache.poi.ss.util.CellRangeAddress;
public class ExcelUtility {
	/**
	 * 创建输出文件的title
	 * @param columnSize
	 * @param sheet
	 * @param title
	 * @param wb
	 */
	public static void createTableHeader(Sheet sheet,Map<String,String> tableTitleMap,Workbook wb){
		Row row = sheet.createRow((short) 1);
		Iterator<Entry<String,String>>  it = tableTitleMap.entrySet().iterator();
		int i = 0;
		
		Font font = setFont(wb);
		CellStyle style = wb.createCellStyle();
		//添加边框
		setCellStyle(style);
		//文字居中
		style.setAlignment(CellStyle.ALIGN_CENTER);
		style.setVerticalAlignment(CellStyle.ALIGN_CENTER);
		style.setFont(font);
		
		while(it.hasNext()){
			Entry<String,String> entry = it.next();
			Cell cell = row.createCell((short) i);
			cell.setCellStyle(style);
			cell.setCellValue(entry.getValue());
			cell.setCellStyle(style);
			i++;
		}
	}
	public static void buildXMExcelDocument(Workbook wb,Sheet sheet,List<Map<Object,Object>> list){
		xmRow1(wb, sheet);
		xmRow2(wb, sheet);
		xmRow3(wb, sheet);
		xmRow4(wb, sheet);
		
		Font font = wb.createFont();
		font.setBoldweight(Font.BOLDWEIGHT_BOLD);
	    font.setFontHeightInPoints((short)15);
	    font.setFontName("宋体");
	    CellStyle style = wb.createCellStyle();
		//文字居中
	    style.setAlignment(CellStyle.ALIGN_CENTER);
	    style.setDataFormat(CellStyle.BORDER_DOUBLE);
//	    style.setVerticalAlignment(CellStyle.ALIGN_CENTER);
	    style.setFont(font);
		for(int i=0;i<4;i++){
			Row row = sheet.getRow(i);
			for(int j=0;j<27;j++){
				Cell cell = row.getCell(j);
				if(cell!=null){
					cell.setCellStyle(style);
				}
			}
		}
		for(int i = 0;i<list.size();i++){
			Map<Object,Object> m = list.get(i);
			Row row = sheet.createRow((short) i+4);
			Map<String,String> map = new LinkedHashMap<String,String>();
			map.put("xm_progress_status_desc", "项目进度");
			map.put("admin_dept", "所属部门");
			map.put("construct_code", "建设单位编号 ");
			map.put("xm_code", "项目编号 ");
			map.put("xm_name", "项目名称 ");
			map.put("end_time", "完工时间");
			map.put("out_invoice", "已开票  ");
			map.put("account_receivable", "应收 ");
			map.put("has_receivable", "实收 ");
			map.put("warranty_value", "应收");
			map.put("already_receive", "已收");
			map.put("already_money", "还/扣垫款");
			map.put("xm_manage_price", "管理费");
			map.put("company_tax", "税金");
			map.put("direct_reimburse", "直接报账额");
			map.put("loan_reimburs", "借款报账额");
			map.put("work_in_invoice", "已收票");
			map.put("already_pay", "应付");
			map.put("pay_value", "进度支付");
			map.put("advancesMoney", "垫款");
			map.put("work_end_pay", "结算支付");
			map.put("work_cut_pay", "扣款");
			map.put("purchase_in_invoice", "已收票");
			map.put("purchase_account_payable", "应付");
			map.put("purchase_has_payable", "已付");
			map.put("already_profit", "已分摊毛利");
			map.put("earn_money", "剩余毛利");
			Iterator<Entry<String,String>>  it = map.entrySet().iterator();
			int j = 0;
			while(it.hasNext()){
				Entry<String,String> entry = it.next();
				Cell cell = row.createCell((short) j);
				if(j<=5){
					cell.setCellValue(m.get(entry.getKey())==null?"":String.valueOf(m.get(entry.getKey())));
				}else{
					if(null != m.get(entry.getKey())){
						cell.setCellValue(Double.parseDouble(String.valueOf(m.get(entry.getKey()))));
					}else{
						cell.setCellValue(Double.parseDouble("0.0"));

					}
				}
				j++;
			}
		}
	}
	public static  void xmRow1(Workbook wb,Sheet sheet){
		//创建第一行
				Row row = sheet.createRow((short) 0);
				
				for(int i=0;i < 27;i++){
					Cell cell = row.createCell((short) i);
				    switch(i){
				    case 0:
				    	cell.setCellValue("项目进度");
				    	break;
				    case 1:
				    	cell.setCellValue("所属部门");
				    	break;
				    case 2:
				    	cell.setCellValue("建设单位编号 ");
				    	break;
				    case 3:
				    	cell.setCellValue("项目编号 ");
				    	break;
				    case 4:
				    	cell.setCellValue("项目名称 ");
				    	break;
				    case 5:
				    	cell.setCellValue("完工时间 ");
				    	break;
				    case 6:
				    	cell.setCellValue("收入 ");
				    	break;
				    case 7:
				    	cell.setCellValue("");
				    	break;
				    case 8:
				    	cell.setCellValue("");
				    	break;
				    case 9:
				    	cell.setCellValue("");
				    	break;
				    case 10:
				    	cell.setCellValue("");
				    	break;
				    case 11:
				    	cell.setCellValue("");
				    	break;
				    case 12:
				    	cell.setCellValue("支出");
				    	break;
				    case 13:
				    	cell.setCellValue("");
				    	break;
				    case 14:
				    	cell.setCellValue("");
				    	break;
				    case 15:
				    	cell.setCellValue("");
				    	break;
				    case 16:
				    	cell.setCellValue("");
				    	break;
				    case 17:
				    	cell.setCellValue("");
				    	break;
				    case 18:
				    	cell.setCellValue("");
				    	break;
				    case 19:
				    	cell.setCellValue("");
				    	break;
				    case 20:
				    	cell.setCellValue("");
				    	break;
				    case 21:
				    	cell.setCellValue("");
				    	break;
				    case 22:
				    	cell.setCellValue("");
				    	break;
				    case 23:
				    	cell.setCellValue("");
				    	break;
				    case 24:
				    	cell.setCellValue("");
				    	break;
				    case 25:
				    	cell.setCellValue("已分摊毛利");
				    	break;
				    case 26:
				    	cell.setCellValue("剩余毛利");
				    	break;
				    default:
				    	cell.setCellValue("");
				    }
				}
				//合并标题单元格
				  sheet.addMergedRegion(new CellRangeAddress(
				            0, //first row (0-based)
				            0, //last row  (0-based)
				            6, //first column (0-based)
				            11  //last column  (0-based)
				    ));
				  sheet.addMergedRegion(new CellRangeAddress(
						  0, //first row (0-based)
						  0, //last row  (0-based)
						  12, //first column (0-based)
						  24  //last column  (0-based)
						  ));
	}
	public static  void xmRow2(Workbook wb,Sheet sheet){
		//创建第2行
		Row row = sheet.createRow((short) 1);
		for(int i=0;i < 27;i++){
			Cell cell = row.createCell((short) i);
			switch(i){
			case 0:
				cell.setCellValue("");
				break;
			case 1:
				cell.setCellValue("");
				break;
			case 2:
				cell.setCellValue("");
				break;
			case 3:
				cell.setCellValue("");
				break;
			case 4:
				cell.setCellValue("");
				break;
			case 5:
				cell.setCellValue("");
				break;
			case 6:
				cell.setCellValue("已开票 ");
				break;
			case 7:
				cell.setCellValue("应收");
				break;
			case 8:
				cell.setCellValue("实收");
				break;
			case 9:
				cell.setCellValue("质保金");
				break;
			case 10:
				cell.setCellValue("");
				break;
			case 11:
				cell.setCellValue("还/扣垫款");
				break;
			case 12:
				cell.setCellValue("管理费");
				break;
			case 13:
				cell.setCellValue("税金");
				break;
			case 14:
				cell.setCellValue("个人结算费");
				break;
			case 15:
				cell.setCellValue("");
				break;
			case 16:
				cell.setCellValue("分包费");
				break;
			case 17:
				cell.setCellValue("");
				break;
			case 18:
				cell.setCellValue("");
				break;
			case 19:
				cell.setCellValue("");
				break;
			case 20:
				cell.setCellValue("");
				break;
			case 21:
				cell.setCellValue("");
				break;
			case 22:
				cell.setCellValue("采购费");
				break;
			case 23:
				cell.setCellValue("");
				break;
			case 24:
				cell.setCellValue("");
				break;
			case 25:
				cell.setCellValue("");
				break;
			case 26:
				cell.setCellValue("");
				break;
			default:
				cell.setCellValue("");
			}
		}
		//合并标题单元格
		sheet.addMergedRegion(new CellRangeAddress(
				1, //first row (0-based)
				1, //last row  (0-based)
				9, //first column (0-based)
				10  //last column  (0-based)
				));
		sheet.addMergedRegion(new CellRangeAddress(
				1, //first row (0-based)
				1, //last row  (0-based)
				14, //first column (0-based)
				15  //last column  (0-based)
				));
		sheet.addMergedRegion(new CellRangeAddress(
				1, //first row (0-based)
				1, //last row  (0-based)
				16, //first column (0-based)
				21  //last column  (0-based)
				));
		sheet.addMergedRegion(new CellRangeAddress(
				1, //first row (0-based)
				1, //last row  (0-based)
				22, //first column (0-based)
				24  //last column  (0-based)
				));
	}
	public static  void xmRow3(Workbook wb,Sheet sheet){
		//创建第3行
		Row row = sheet.createRow((short) 2);
		for(int i=0;i < 27;i++){
			Cell cell = row.createCell((short) i);
			switch(i){
			case 0:
				cell.setCellValue("");
				break;
			case 1:
				cell.setCellValue("");
				break;
			case 2:
				cell.setCellValue("");
				break;
			case 3:
				cell.setCellValue("");
				break;
			case 4:
				cell.setCellValue("");
				break;
			case 5:
				cell.setCellValue(" ");
				break;
			case 6:
				cell.setCellValue("");
				break;
			case 7:
				cell.setCellValue("");
				break;
			case 8:
				cell.setCellValue("");
				break;
			case 9:
				cell.setCellValue("应收");
				break;
			case 10:
				cell.setCellValue("已收");
				break;
			case 11:
				cell.setCellValue("");
				break;
			case 12:
				cell.setCellValue("");
				break;
			case 13:
				cell.setCellValue("");
				break;
			case 14:
				cell.setCellValue("直接报账额");
				break;
			case 15:
				cell.setCellValue("借款报账额");
				break;
			case 16:
				cell.setCellValue("已收票");
				break;
			case 17:
				cell.setCellValue("应付");
				break;
			case 18:
				cell.setCellValue("实付");
				break;
			case 19:
				cell.setCellValue("");
				break;
			case 20:
				cell.setCellValue("");
				break;
			case 21:
				cell.setCellValue("扣款");
				break;
			case 22:
				cell.setCellValue("已收票");
				break;
			case 23:
				cell.setCellValue("应付");
				break;
			case 24:
				cell.setCellValue("已付");
				break;
			case 25:
				cell.setCellValue("");
				break;
			case 26:
				cell.setCellValue("");
				break;
			default:
				cell.setCellValue("");
			}
		}
		//合并标题单元格
		sheet.addMergedRegion(new CellRangeAddress(
				2, //first row (0-based)
				2, //last row  (0-based)
				18, //first column (0-based)
				20  //last column  (0-based)
				));
	}
	public static  void xmRow4(Workbook wb,Sheet sheet){
		//创建第4行
		Row row = sheet.createRow((short) 3);
		for(int i=0;i < 27;i++){
			Cell cell = row.createCell((short) i);
			switch(i){
			case 0:
				cell.setCellValue("");
				break;
			case 1:
				cell.setCellValue("");
				break;
			case 2:
				cell.setCellValue("");
				break;
			case 3:
				cell.setCellValue("");
				break;
			case 4:
				cell.setCellValue("");
				break;
			case 5:
				cell.setCellValue(" ");
				break;
			case 6:
				cell.setCellValue("");
				break;
			case 7:
				cell.setCellValue("");
				break;
			case 8:
				cell.setCellValue("");
				break;
			case 9:
				cell.setCellValue("");
				break;
			case 10:
				cell.setCellValue("");
				break;
			case 11:
				cell.setCellValue("");
				break;
			case 12:
				cell.setCellValue("");
				break;
			case 13:
				cell.setCellValue("");
				break;
			case 14:
				cell.setCellValue("");
				break;
			case 15:
				cell.setCellValue("");
				break;
			case 16:
				cell.setCellValue("");
				break;
			case 17:
				cell.setCellValue("");
				break;
			case 18:
				cell.setCellValue("进度支付");
				break;
			case 19:
				cell.setCellValue("垫款");
				break;
			case 20:
				cell.setCellValue("结算支付");
				break;
			case 21:
				cell.setCellValue("");
				break;
			case 22:
				cell.setCellValue("");
				break;
			case 23:
				cell.setCellValue("");
				break;
			case 24:
				cell.setCellValue("");
				break;
			case 25:
				cell.setCellValue("");
				break;
			case 26:
				cell.setCellValue("");
				break;
			default:
				cell.setCellValue("");
			}
		}
		sheet.addMergedRegion(new CellRangeAddress(
				0, //first row (0-based)
				3, //last row  (0-based)
				0, //first column (0-based)
				0  //last column  (0-based)
				));
		sheet.addMergedRegion(new CellRangeAddress(
				0, //first row (0-based)
				3, //last row  (0-based)
				1, //first column (0-based)
				1  //last column  (0-based)
				));
		sheet.addMergedRegion(new CellRangeAddress(
				0, //first row (0-based)
				3, //last row  (0-based)
				2, //first column (0-based)
				2  //last column  (0-based)
				));
		sheet.addMergedRegion(new CellRangeAddress(
				0, //first row (0-based)
				3, //last row  (0-based)
				3, //first column (0-based)
				3  //last column  (0-based)
				));
		sheet.addMergedRegion(new CellRangeAddress(
				0, //first row (0-based)
				3, //last row  (0-based)
				4, //first column (0-based)
				4  //last column  (0-based)
				));
		sheet.addMergedRegion(new CellRangeAddress(
				0, //first row (0-based)
				3, //last row  (0-based)
				5, //first column (0-based)
				5  //last column  (0-based)
				));
		sheet.addMergedRegion(new CellRangeAddress(
				1, //first row (0-based)
				3, //last row  (0-based)
				6, //first column (0-based)
				6  //last column  (0-based)
				));
		sheet.addMergedRegion(new CellRangeAddress(
				1, //first row (0-based)
				3, //last row  (0-based)
				7, //first column (0-based)
				7  //last column  (0-based)
				));
		sheet.addMergedRegion(new CellRangeAddress(
				1, //first row (0-based)
				3, //last row  (0-based)
				8, //first column (0-based)
				8  //last column  (0-based)
				));
		sheet.addMergedRegion(new CellRangeAddress(
				2, //first row (0-based)
				3, //last row  (0-based)
				9, //first column (0-based)
				9  //last column  (0-based)
				));
		sheet.addMergedRegion(new CellRangeAddress(
				2, //first row (0-based)
				3, //last row  (0-based)
				10, //first column (0-based)
				10  //last column  (0-based)
				));
		sheet.addMergedRegion(new CellRangeAddress(
				1, //first row (0-based)
				3, //last row  (0-based)
				11, //first column (0-based)
				11  //last column  (0-based)
				));
		sheet.addMergedRegion(new CellRangeAddress(
				1, //first row (0-based)
				3, //last row  (0-based)
				12, //first column (0-based)
				12  //last column  (0-based)
				));
		sheet.addMergedRegion(new CellRangeAddress(
				1, //first row (0-based)
				3, //last row  (0-based)
				13, //first column (0-based)
				13  //last column  (0-based)
				));
		sheet.addMergedRegion(new CellRangeAddress(
				2, //first row (0-based)
				3, //last row  (0-based)
				14, //first column (0-based)
				14  //last column  (0-based)
				));
		sheet.addMergedRegion(new CellRangeAddress(
				2, //first row (0-based)
				3, //last row  (0-based)
				15, //first column (0-based)
				15  //last column  (0-based)
				));
		sheet.addMergedRegion(new CellRangeAddress(
				2, //first row (0-based)
				3, //last row  (0-based)
				16, //first column (0-based)
				16  //last column  (0-based)
				));
		sheet.addMergedRegion(new CellRangeAddress(
				2, //first row (0-based)
				3, //last row  (0-based)
				17, //first column (0-based)
				17  //last column  (0-based)
				));
		sheet.addMergedRegion(new CellRangeAddress(
				2, //first row (0-based)
				3, //last row  (0-based)
				21, //first column (0-based)
				21  //last column  (0-based)
				));
		sheet.addMergedRegion(new CellRangeAddress(
				2, //first row (0-based)
				3, //last row  (0-based)
				22, //first column (0-based)
				22  //last column  (0-based)
				));
		sheet.addMergedRegion(new CellRangeAddress(
				2, //first row (0-based)
				3, //last row  (0-based)
				23, //first column (0-based)
				23  //last column  (0-based)
				));
		sheet.addMergedRegion(new CellRangeAddress(
				2, //first row (0-based)
				3, //last row  (0-based)
				24, //first column (0-based)
				24  //last column  (0-based)
				));
		sheet.addMergedRegion(new CellRangeAddress(
				0, //first row (0-based)
				3, //last row  (0-based)
				25, //first column (0-based)
				25  //last column  (0-based)
				));
		sheet.addMergedRegion(new CellRangeAddress(
				0, //first row (0-based)
				3, //last row  (0-based)
				26, //first column (0-based)
				26  //last column  (0-based)
				));
	}
	@SuppressWarnings("unchecked")
	public static void shareExcelOut(Sheet sheet,Workbook wb,Map<String,String> tableTitleMap,List<Map<Object,Object>> list){
		CellStyle style1 = wb.createCellStyle();//黄色     业务信息
		//添加边框
		setCellStyle(style1);
		//文字居中
		style1.setAlignment(CellStyle.ALIGN_CENTER);
		style1.setVerticalAlignment(CellStyle.ALIGN_CENTER);
		style1.setFillForegroundColor(IndexedColors.YELLOW.getIndex());
		style1.setFillPattern(CellStyle.SOLID_FOREGROUND);
		
		CellStyle style2 = wb.createCellStyle();//黑体    明细头
		//添加边框
		setCellStyle(style2);
		//文字居中
		style2.setAlignment(CellStyle.ALIGN_CENTER);
		style2.setVerticalAlignment(CellStyle.ALIGN_CENTER);
		Font font = setFont(wb);
		style2.setFont(font);
		style2.setFillForegroundColor(IndexedColors.GREY_25_PERCENT.getIndex());
		style2.setFillPattern(CellStyle.SOLID_FOREGROUND);
		
		CellStyle style3 = wb.createCellStyle();//明细信息
		//添加边框
		setCellStyle(style3);
		//文字居中
		style3.setAlignment(CellStyle.ALIGN_CENTER);
		style3.setVerticalAlignment(CellStyle.ALIGN_CENTER);
		style3.setFillForegroundColor(IndexedColors.WHITE.getIndex());
		style3.setFillPattern(CellStyle.SOLID_FOREGROUND);
		for(int i = 0;i<list.size();i++){
			Map<Object,Object> m = list.get(i);
			Row row = sheet.createRow((short) i+2);
			Iterator<Entry<String,String>>  it = tableTitleMap.entrySet().iterator();
			int j = 0;
			while(it.hasNext()){
				Entry<String,String> entry = it.next();
				Cell cell = row.createCell((short) j);
				cell.setCellStyle(style1);
				cell.setCellValue(m.get(entry.getKey())==null?"":String.valueOf(m.get(entry.getKey())));
				j++;
			}
		}
		
	}
	
	/**
	 * 创建输出文件的title
	 * @param columnSize
	 * @param sheet
	 * @param title
	 * @param wb
	 */
	public static void createHeader(int columnSize, Sheet sheet,String title,Workbook wb){
		Row row = sheet.createRow((short) 0);
		for(int i=0;i<columnSize;i++){
			Cell cell = row.createCell((short) i);
			
			CellStyle style = wb.createCellStyle();
			//文字居中
		    style.setAlignment(CellStyle.ALIGN_CENTER);
		    style.setVerticalAlignment(CellStyle.ALIGN_CENTER);
		    
			if(i == 0){
				Font font = wb.createFont();
				font.setBoldweight(Font.BOLDWEIGHT_BOLD);
			    font.setFontHeightInPoints((short)24);
			    font.setFontName("宋体");
			    style.setFont(font);
			    cell.setCellStyle(style);
				cell.setCellValue(title);
			}
			cell.setCellStyle(style);
		}
		//合并标题单元格
		  sheet.addMergedRegion(new CellRangeAddress(
		            0, //first row (0-based)
		            0, //last row  (0-based)
		            0, //first column (0-based)
		            columnSize-1  //last column  (0-based)
		    ));
	}
	
	/**
	 * 对数据表格添加边框
	 * @param style
	 */
	public static void setCellStyle(CellStyle style){
		style.setBorderBottom(CellStyle.BORDER_THIN );
	    style.setBottomBorderColor(IndexedColors.BLACK.getIndex());
	    style.setBorderLeft(CellStyle.BORDER_THIN);
	    style.setLeftBorderColor(IndexedColors.BLACK.getIndex());
	    style.setBorderRight(CellStyle.BORDER_THIN );
	    style.setRightBorderColor(IndexedColors.BLACK.getIndex());
	    style.setBorderTop(CellStyle.BORDER_THIN);
	    style.setTopBorderColor(IndexedColors.BLACK.getIndex());
	}
	/**
	 * 设置表头的字体 大小
	 * @param wb
	 * @return
	 */
	public static Font setFont(Workbook wb){
		Font font = wb.createFont();
		font.setBoldweight(Font.BOLDWEIGHT_BOLD);
	    font.setFontHeightInPoints((short)10);
	    font.setFontName("宋体");
	    return font;
	}
	public static Object getCellValue(CellValue cellValue){
		if(cellValue == null){return "";}
		switch (cellValue.getCellType()) {
		    case Cell.CELL_TYPE_BOOLEAN:
		    	return cellValue.getBooleanValue();
		    case Cell.CELL_TYPE_NUMERIC:
		    	return cellValue.getNumberValue();
		    case Cell.CELL_TYPE_STRING:
		        return cellValue.getStringValue();
		    case Cell.CELL_TYPE_BLANK:
		    	return "";
		    case Cell.CELL_TYPE_ERROR:
		    	return "";
	
		    // CELL_TYPE_FORMULA will never happen
		    case Cell.CELL_TYPE_FORMULA: 
		    	System.out.println("CELL_TYPE_FORMULA");
		    	return "";
		    default:
		           return "";
		}				
	}
}
